from sqlalchemy import Column, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker


DB_FILES = "db/database.sqlite3"
# 创建数据库引擎
engine = create_engine("sqlite:///" + DB_FILES, echo=True)
Base = declarative_base()
# 声明模型类
class User(Base):
    # 指定表名
    __tablename__ = "users"
    # 设置user_id字段
    userid = Column("user_id", String(100), primary_key=True)
    # 设置用户名字段
    username = Column("username", String(100))
    #  设置电话字段
    phone = Column("phone", String(100))
    # 设置邮箱字段
    email = Column("email", String(100))

def init_db():
    # 初始化数据，从模型创建数据库表
    Base.metadata.create_all(engine)


# 实现查询所有数据
def find_all():
    # 获得会话工厂类
    Session = sessionmaker(bind=engine)
    # 创建会话对象
    session = Session()
    # 所有数据
    list1 = session.query(User).all()
    # list1包括User模型的列表
    # 包括User模型的列表转换为包含字典的列表
    # 包含字典的列表
    list2 = []
    for item in list1:
        dict = {}
        dict["user_id"] = item.userid
        dict["username"] = item.username
        dict["username"] = item.username
        dict["phone"] = item.phone
        dict["email"] = item.email
        # 把字典放到列表中
        list2.append(dict)
    # 关闭Session
    session.close()
    return list2
# 插入数据
def create(user):
    # 获得会话工厂类
    Session = sessionmaker(bind=engine)
    # 创建会话对象
    session = Session()
    # 将字典的user对象转换User模型对象
    new_user = User()
    new_user.userid = user["user_id"]
    new_user.username = user["username"]
    new_user.email = user["email"]
    new_user.phone = user["phone"]
    try:
        # 插入数据到数据
        session.add(new_user)
        # 提交事务
        session.commit()
        return 1 # 插入成功
    except:
        # 回滚事务
        session.rollback()
    finally:
        # 关闭Session
        session.close()
    return -1 # 插入失败

# 数据删除
def remove(userid):
    # 获得会话工厂类
    Session = sessionmaker(bind=engine)
    # 创建会话对象
    session = Session()
    try:
        user = session.query(User).filter_by(userid=userid).one()
        # 如果用户存在删除
        session.delete(user)
        # 提交数据库事务
        session.commit()
        return 1 # 成功删除数据
    except:
        # 回滚数据库事务
        session.rollback() 
    finally:
        # 关闭Session
        session.close()
    
    return -1 # 删除失败



# 更新数据
def modify(user):
    # 获得会话工厂类
    Session = sessionmaker(bind=engine)
    # 创建会话对象
    session = Session()
    try:
            # 查询出一个要修改的用户模型对象
            user2 = session.query(User).filter_by(userid=user["user_id"]).one()
            user2.email = user["email"]
            user2.username = user["username"]
            user2.phone = user["phone"]
            # 提交数据库事务 
            session.commit()
            return 1 #-表示成功
    except:
        # 回滚数据库事务 
        session.rollback()
    finally:
        # 关闭Session
        session.close()
    return -1 #-1表示失败

# 按主键查询用户
def find_by_id(userid):
    # 获得会话工厂类
    Session = sessionmaker(bind=engine)
    # 创建会话对象
    session = Session()
    user = session.query(User).filter_by(userid=userid).one()

    dict = {}
    dict["userid"] = user.userid
    dict["username"] = user.username
    dict["username"] = user.username
    dict["phone"] = user.phone
    dict["email"] = user.email

    # 关闭Session
    session.close()

    return dict

# 用于测试
if __name__ == "__main__":
    # 数据库初始化
    init_db()

    # user = find_by_id('tony12')
    # print(user)

    # lists = find_all()
    # print(lists)

    # # 测试
    # user = {}
    # user["user_id"] = "Tony123"
    # user["userid"] = "Tony123"
    # user["username"] = "guan"
    # user["email"] = "Tony123@sina.com"
    # user["phone"] = "1233456"

    # result = create(user)
    # print(result)
    # lists = find_all()
    # print(lists)

    # result = remove("Tony123")
    # print(result)


    # lists = find_all()
    # print(lists)

    # user = {}
    # user["userid"] = "tony12"
    # user["username"] = "李四SC"
    # user["email"] = "Tony3@sina.com"
    # user["phone"] = "ABC"

    # result = modify(user)
    # print(result)
    # lists = find_all()
    # print(lists)
